package com.wcpdoc.survey.survey.dao.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.wcpdoc.survey.core.dao.impl.BaseDaoImpl;
import com.wcpdoc.survey.core.entity.LoginUser;
import com.wcpdoc.survey.core.entity.PageIn;
import com.wcpdoc.survey.core.entity.PageOut;
import com.wcpdoc.survey.core.util.DateUtil;
import com.wcpdoc.survey.core.util.HibernateUtil;
import com.wcpdoc.survey.core.util.SqlUtil;
import com.wcpdoc.survey.core.util.SqlUtil.Order;
import com.wcpdoc.survey.core.util.ValidateUtil;
import com.wcpdoc.survey.survey.dao.SurveyDao;
import com.wcpdoc.survey.survey.entity.Question;
import com.wcpdoc.survey.survey.entity.QuestionEx;
import com.wcpdoc.survey.survey.entity.Survey;
import com.wcpdoc.survey.survey.entity.SurveyType;
import com.wcpdoc.survey.sys.cache.DictCache;

/**
 * 调查数据访问层实现
 * 
 * v1.0 zhanghc 2017-06-11 09:13:23
 */
@Repository
public class SurveyDaoImpl extends BaseDaoImpl<Survey> implements SurveyDao {

	@Override
	public PageOut getListpage(PageIn pageIn) {
		String sql = "SELECT SURVEY.ID, SURVEY.NAME, SURVEY.START_TIME, SURVEY.END_TIME, "
				+ "SURVEY.STATE, PAPER.NAME AS PAPER_NAME, SURVEY_TYPE.NAME AS SURVEY_TYPE_NAME "
				+ "FROM SV_SURVEY SURVEY "
				+ "LEFT JOIN SV_SURVEY_TYPE SURVEY_TYPE ON SURVEY.SURVEY_TYPE_ID = SURVEY_TYPE.ID "
				+ "LEFT JOIN SV_PAPER PAPER ON SURVEY.PAPER_ID = PAPER.ID";
		
		SqlUtil sqlUtil = new SqlUtil(sql);
		sqlUtil.addWhere(ValidateUtil.isValid(pageIn.getOne()) && !"1".equals(pageIn.getOne()), "SURVEY.SURVEY_TYPE_ID = ?", pageIn.getOne())
				.addWhere(ValidateUtil.isValid(pageIn.getTwo()), "SURVEY.NAME LIKE ?", "%" + pageIn.getTwo() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getThree()), "PAPER.NAME LIKE ?", "%" + pageIn.getThree() + "%")
//				.addWhere(ValidateUtil.isValid(pageIn.getFour()), "QUESTION.STATE = ?", pageIn.getFour())//0：删除；1：启用；2：禁用
//				.addWhere(ValidateUtil.isValid(pageIn.getFive()), "QUESTION_YTPE.NAME LIKE ?", "%" + pageIn.getFive() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getTen()), "SURVEY.STATE = ?", pageIn.getTen())
				.addWhere("SURVEY.STATE != ?", 0)
				.addOrder("SURVEY.START_TIME", Order.DESC);
		PageOut pageOut = getListpage(sqlUtil, pageIn);
		HibernateUtil.formatDate(pageOut.getRows(), "START_TIME", DateUtil.FORMAT_DATE_TIME, "END_TIME", DateUtil.FORMAT_DATE_TIME);
		HibernateUtil.formatDict(pageOut.getRows(), DictCache.getIndexkeyValueMap(), "SURVEY_STATE", "STATE");
		return pageOut;
	}

	@Override
	public PageOut getSurveyUserListpage(PageIn pageIn) {
		String sql = "SELECT SURVEY_USER.ID, USER.NAME AS USER_NAME, USER.LOGIN_NAME, ORG.NAME AS ORG_NAME, POST_USER.POST_NAMES "
				+ "FROM SV_SURVEY_USER SURVEY_USER "
				+ "INNER JOIN SYS_USER USER ON SURVEY_USER.USER_ID = USER.ID "
				+ "INNER JOIN SYS_ORG ORG ON USER.ORG_ID = ORG.ID "
				+ "LEFT JOIN (SELECT POST_USER.USER_ID, GROUP_CONCAT(POST.NAME) AS POST_NAMES "
				+ "				FROM SYS_POST_USER POST_USER "
				+ "				INNER JOIN SYS_POST POST ON POST_USER.POST_ID = POST.ID "
				+ "				GROUP BY POST_USER.USER_ID ) POST_USER ON USER.ID = POST_USER.USER_ID";
		SqlUtil sqlUtil = new SqlUtil(sql);
		sqlUtil.addWhere(ValidateUtil.isValid(pageIn.getOne()) && !"1".equals(pageIn.getOne()), "ORG.ID = ?", pageIn.getOne())
				.addWhere(ValidateUtil.isValid(pageIn.getTwo()), "USER.NAME LIKE ?", "%" + pageIn.getTwo() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getTen()), "SURVEY_USER.SURVEY_ID = ?", pageIn.getTen())
				.addWhere("USER.STATE = ?", 1)
				.addWhere("USER.ID != ?", 1)
				.addWhere("ORG.STATE = ?", 1)
				.addOrder("USER.NAME", Order.DESC);
		
		PageOut pageOut = getListpage(sqlUtil, pageIn);
		return pageOut;
	}

	@Override
	public PageOut getSurveyUserAddListpage(PageIn pageIn) {
		String sql = "SELECT USER.ID, USER.NAME, USER.LOGIN_NAME, ORG.NAME AS ORG_NAME, POST_USER.POST_NAMES "
				+ "FROM SYS_USER USER "
				+ "INNER JOIN SYS_ORG ORG ON USER.ORG_ID = ORG.ID "
				+ "LEFT JOIN (SELECT POST_USER.USER_ID, GROUP_CONCAT(POST.NAME) AS POST_NAMES "
				+ "				FROM SYS_POST_USER POST_USER "
				+ "				INNER JOIN SYS_POST POST ON POST_USER.POST_ID = POST.ID "
				+ "				GROUP BY POST_USER.USER_ID ) POST_USER ON USER.ID = POST_USER.USER_ID";
		SqlUtil sqlUtil = new SqlUtil(sql);
		sqlUtil.addWhere(ValidateUtil.isValid(pageIn.getOne()) && !"1".equals(pageIn.getOne()), "ORG.ID = ?", pageIn.getOne())
				.addWhere(ValidateUtil.isValid(pageIn.getTwo()), "USER.NAME LIKE ?", "%" + pageIn.getTwo() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getTen()), "NOT EXISTS (SELECT 1 FROM SV_SURVEY_USER Z WHERE Z.SURVEY_ID = ? AND USER.ID = Z.USER_ID)", pageIn.getTen())
				.addWhere("USER.STATE = ?", 1)
				.addWhere("USER.ID != ?", 1)
				.addWhere("ORG.STATE = ?", 1)
				.addOrder("USER.NAME", Order.DESC);
		
		PageOut pageOut = getListpage(sqlUtil, pageIn);
		return pageOut;
	}

	@Override
	public void doUserDel(Integer id, Integer userId) {
		String sql = "DELETE FROM SV_SURVEY_USER_QUESTION WHERE SURVEY_ID = ? AND USER_ID = ?";
		update(sql, id, userId);
	}

	@Override
	public List<Map<String, Object>> getUnFinishList(Integer userId) {
		String sql = "SELECT SURVEY.ID AS SURVEY_ID, SURVEY.NAME AS SURVEY_NAME, SURVEY.DESCRIPTION AS SURVEY_DESCRIPTION, SURVEY_USER.ID  AS SURVEY_USER_ID "
				+ "FROM SV_SURVEY SURVEY "
				+ "INNER JOIN SV_SURVEY_USER SURVEY_USER ON SURVEY.ID = SURVEY_USER.SURVEY_ID "
				+ "WHERE SURVEY.STATE = 1 AND SURVEY_USER.USER_ID = ? AND SURVEY.END_TIME >= ? ";
		return getList(sql, new Object[]{userId, new Date()});
	}

	@Override
	public SurveyType getSurveyType(Integer id) {
		String sql = "SELECT SURVEY_TYPE.* "
				+ "FROM SV_SURVEY_TYPE SURVEY_TYPE "
				+ "INNER JOIN SV_SURVEY SURVEY ON SURVEY_TYPE.ID = SURVEY.SURVEY_TYPE_ID "
				+ "WHERE SURVEY.ID = ?";
		return getUnique(sql, new Object[]{id}, SurveyType.class);
	}

	@Override
	public List<Survey> getList(Integer surveyTypeId) {
		String sql = "SELECT * FROM SV_SURVEY SURVEY_TYPE WHERE STATE = 1 AND SURVEY_TYPE_ID = ?";
		return getList(sql, new Object[]{surveyTypeId}, Survey.class);
	}

	@Override
	public PageOut getMySurveyListpage(PageIn pageIn) {
		String sql = "SELECT SURVEY_USER.ID, SURVEY.NAME AS SURVEY_NAME, PAPER.NAME AS PAPER_NAME, SURVEY.START_TIME AS SURVEY_START_TIME, "
				+ "		SURVEY.END_TIME AS SURVEY_END_TIME, "
				+ "		SURVEY_USER.STATE AS SURVEY_USER_STATE, SURVEY_USER.TOTAL_SCORE AS SURVEY_USER_TOTAL_SCORE "
				+ "FROM SV_SURVEY_USER SURVEY_USER "
				+ "INNER JOIN SV_SURVEY SURVEY ON SURVEY_USER.SURVEY_ID = SURVEY.ID "
				+ "INNER JOIN SV_PAPER PAPER ON SURVEY.PAPER_ID = PAPER.ID "
				+ "INNER JOIN SYS_USER USER ON SURVEY_USER.USER_ID = USER.ID";
		
		SqlUtil sqlUtil = new SqlUtil(sql);
		sqlUtil.addWhere(ValidateUtil.isValid(pageIn.getTen()), "SURVEY_USER.USER_ID =  ?", pageIn.getTen())
				.addWhere("SURVEY.STATE = ?", 1)
//				.addWhere("PAPER.STATE = ?", 1)//删除了问卷也能查看
				.addWhere("USER.STATE = ?", 1)
				.addOrder("SURVEY.START_TIME", Order.DESC);
		PageOut pageOut = getListpage(sqlUtil, pageIn);
		HibernateUtil.formatDate(pageOut.getRows(), "SURVEY_START_TIME", DateUtil.FORMAT_DATE_TIME, "SURVEY_END_TIME", DateUtil.FORMAT_DATE_TIME);
		HibernateUtil.formatDict(pageOut.getRows(), DictCache.getIndexkeyValueMap(), "SURVEY_USER_STATE", "SURVEY_USER_STATE");
		return pageOut;
	}

	@Override
	public PageOut getMarkUserListpage(PageIn pageIn) {
		String sql = "SELECT MARK_USER.ID, USER.NAME AS USER_NAME, USER.LOGIN_NAME, ORG.NAME AS ORG_NAME, POST_USER.POST_NAMES "
				+ "FROM EX_MARK_USER MARK_USER "
				+ "INNER JOIN SYS_USER USER ON MARK_USER.USER_ID = USER.ID "
				+ "INNER JOIN SYS_ORG ORG ON USER.ORG_ID = ORG.ID "
				+ "LEFT JOIN (SELECT POST_USER.USER_ID, GROUP_CONCAT(POST.NAME) AS POST_NAMES "
				+ "				FROM SYS_POST_USER POST_USER "
				+ "				INNER JOIN SYS_POST POST ON POST_USER.POST_ID = POST.ID "
				+ "				GROUP BY POST_USER.USER_ID ) POST_USER ON USER.ID = POST_USER.USER_ID";
		SqlUtil sqlUtil = new SqlUtil(sql);
		sqlUtil.addWhere(ValidateUtil.isValid(pageIn.getOne()) && !"1".equals(pageIn.getOne()), "ORG.ID = ?", pageIn.getOne())
				.addWhere(ValidateUtil.isValid(pageIn.getTwo()), "USER.NAME LIKE ?", "%" + pageIn.getTwo() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getTen()), "MARK_USER.SURVEY_ID = ?", pageIn.getTen())
				.addWhere("USER.STATE = ?", 1)
				.addWhere("USER.ID != ?", 1)
				.addWhere("ORG.STATE = ?", 1)
				.addOrder("USER.NAME", Order.DESC);
		
		PageOut pageOut = getListpage(sqlUtil, pageIn);
		return pageOut;
	}

	@Override
	public PageOut getMarkUserAddListpage(PageIn pageIn) {
		String sql = "SELECT USER.ID, USER.NAME, USER.LOGIN_NAME, ORG.NAME AS ORG_NAME, POST_USER.POST_NAMES "
				+ "FROM SYS_USER USER "
				+ "INNER JOIN SYS_ORG ORG ON USER.ORG_ID = ORG.ID "
				+ "LEFT JOIN (SELECT POST_USER.USER_ID, GROUP_CONCAT(POST.NAME) AS POST_NAMES "
				+ "				FROM SYS_POST_USER POST_USER "
				+ "				INNER JOIN SYS_POST POST ON POST_USER.POST_ID = POST.ID "
				+ "				GROUP BY POST_USER.USER_ID ) POST_USER ON USER.ID = POST_USER.USER_ID";
		SqlUtil sqlUtil = new SqlUtil(sql);
		sqlUtil.addWhere(ValidateUtil.isValid(pageIn.getOne()) && !"1".equals(pageIn.getOne()), "ORG.ID = ?", pageIn.getOne())
				.addWhere(ValidateUtil.isValid(pageIn.getTwo()), "USER.NAME LIKE ?", "%" + pageIn.getTwo() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getTen()), "NOT EXISTS (SELECT 1 FROM EX_MARK_USER Z WHERE Z.SURVEY_ID = ? AND USER.ID = Z.USER_ID)", pageIn.getTen())
				.addWhere("USER.STATE = ?", 1)
				.addWhere("USER.ID != ?", 1)
				.addWhere("ORG.STATE = ?", 1)
				.addOrder("USER.NAME", Order.DESC);
		
		PageOut pageOut = getListpage(sqlUtil, pageIn);
		return pageOut;
	}

	@Override
	public PageOut getMarkPaperListpage(PageIn pageIn) {
		String sql = "SELECT SURVEY_USER.ID, SURVEY.NAME AS SURVEY_NAME, PAPER.NAME AS PAPER_NAME, USER.NAME AS USER_NAME, ORG.NAME AS ORG_NAME, "
				+ "		SURVEY.START_TIME AS SURVEY_START_TIME, SURVEY.END_TIME AS SURVEY_END_TIME, "
				+ "		SURVEY_USER.TOTAL_SCORE AS SURVEY_USER_TOTAL_SCORE, "
				+ "		SURVEY_USER.STATE AS SURVEY_USER_STATE "
				+ "FROM EX_MARK_USER MARK_USER "
				+ "INNER JOIN SV_SURVEY SURVEY ON MARK_USER.SURVEY_ID = SURVEY.ID "
				+ "INNER JOIN SV_PAPER PAPER ON SURVEY.PAPER_ID = PAPER.ID "
				+ "INNER JOIN SV_SURVEY_USER SURVEY_USER ON SURVEY.ID = SURVEY_USER.SURVEY_ID "
				+ "INNER JOIN SYS_USER USER ON SURVEY_USER.USER_ID = USER.ID "
				+ "INNER JOIN SYS_ORG ORG ON USER.ORG_ID = ORG.ID ";
		SqlUtil sqlUtil = new SqlUtil(sql);
		sqlUtil.addWhere(ValidateUtil.isValid(pageIn.getTen()), "MARK_USER.USER_ID = ?", pageIn.getTen())
				.addWhere("USER.STATE = ?", 1)
				.addWhere("ORG.STATE = ?", 1)
				.addWhere("SURVEY.STATE = ?", 1)
//				.addWhere("PAPER.STATE = ?", 1)//这个可以无效
				.addOrder("SURVEY.START_TIME", Order.DESC)
				.addOrder("USER.ID", Order.ASC);
		
		PageOut pageOut = getListpage(sqlUtil, pageIn);
		HibernateUtil.formatDate(pageOut.getRows(), "SURVEY_START_TIME", DateUtil.FORMAT_DATE_TIME, "SURVEY_END_TIME", DateUtil.FORMAT_DATE_TIME);
		HibernateUtil.formatDict(pageOut.getRows(), DictCache.getIndexkeyValueMap(), "SURVEY_USER_STATE", "SURVEY_USER_STATE");
		return pageOut;
	}

	@Override
	public void doForcePaper(LoginUser user) {
		String sql = "UPDATE SV_SURVEY_USER "
				+ "SET STATE = 4, UPDATE_USER_ID = ?, UPDATE_TIME = ? "
				+ "WHERE STATE <= 2 AND ID IN (SELECT ID FROM("
				+ "		SELECT Z.ID "
				+ "		FROM SV_SURVEY_USER Z "
				+ "		INNER JOIN SV_SURVEY Z1 ON Z.SURVEY_ID = Z1.ID "
				+ "		WHERE Z1.END_TIME <= ?) A)";
		Date curTime = new Date();
		update(sql, new Object[]{user.getId(), curTime, curTime});
	}

//	@Override
//	public List<MarkUser> getMarkUserList(Integer id) {
//		String sql = "SELECT * FROM EX_MARK_USER WHERE SURVEY_ID = ?";
//		return getList(sql, new Object[]{id}, MarkUser.class);
//	}

	@Override
	public PageOut getGradeListpage(PageIn pageIn) {
		String sql = "SELECT SURVEY.NAME AS SURVEY_NAME, SURVEY.START_TIME AS SURVEY_START_TIME, "
				+ "		SURVEY.END_TIME AS SURVEY_END_TIME, PAPER.NAME AS PAPER_NAME, USER.NAME AS USER_NAME, "
				+ "		SURVEY_USER.TOTAL_SCORE, SURVEY_USER.STATE AS SURVEY_USER_STATE, MARK_USER.NAME AS MARK_USER_NAME, "
				+ "		SURVEY_USER.UPDATE_MARK_TIME AS SURVEY_USER_UPDATE_MARK_TIME "
				+ "FROM SV_SURVEY_USER SURVEY_USER "
				+ "INNER JOIN SV_SURVEY SURVEY ON SURVEY.ID = SURVEY_USER.SURVEY_ID "
				+ "INNER JOIN SV_PAPER PAPER ON SURVEY.PAPER_ID = PAPER.ID "
				+ "INNER JOIN SYS_USER USER ON SURVEY_USER.USER_ID = USER.ID "
				+ "INNER JOIN SYS_USER MARK_USER ON SURVEY_USER.UPDATE_MARK_USER_ID = MARK_USER.ID ";
		
		SqlUtil sqlUtil = new SqlUtil(sql);
		sqlUtil//.addWhere(ValidateUtil.isValid(pageIn.getOne()) && !"1".equals(pageIn.getOne()), "SURVEY.SURVEY_TYPE_ID = ?", pageIn.getOne())
				.addWhere(ValidateUtil.isValid(pageIn.getTwo()), "SURVEY.NAME LIKE ?", "%" + pageIn.getTwo() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getThree()), "PAPER.NAME LIKE ?", "%" + pageIn.getThree() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getFour()), "USER.NAME LIKE ?", "%" + pageIn.getFour() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getFive()), "MARK.USER.NAME LIKE ?", "%" + pageIn.getFive() + "%")
				.addWhere(ValidateUtil.isValid(pageIn.getSix()), "SURVEY_USER.STATE = ?", pageIn.getSix())
				.addOrder("SURVEY.START_TIME", Order.DESC);
		PageOut pageOut = getListpage(sqlUtil, pageIn);
		HibernateUtil.formatDate(pageOut.getRows(), "SURVEY_START_TIME", DateUtil.FORMAT_DATE_TIME, 
				"SURVEY_END_TIME", DateUtil.FORMAT_DATE_TIME, "SURVEY_USER_UPDATE_MARK_TIME", DateUtil.FORMAT_DATE_TIME);
		HibernateUtil.formatDict(pageOut.getRows(), DictCache.getIndexkeyValueMap(), "SURVEY_USER_STATE", "SURVEY_USER_STATE");
		return pageOut;
	}
	
	@Override
	public Map<String, Object> getChartData(int surveyId, int questionId) {
		StringBuilder sql = new StringBuilder("SELECT * FROM SV_QUESTION WHERE ID = ?");
		Question question = getUnique(sql.toString(), new Object[]{questionId}, Question.class);
		if(question.getType() == 9 || question.getType() == 10){
			return new HashMap<>();
		}
		
		QuestionEx questionEx = new QuestionEx(question);
		sql = new StringBuilder();
		sql.append("SELECT ");
		for(int i = 0; i < questionEx.getAllIndexList().size(); i++){
			String index = questionEx.getAllIndexList().get(i);
			sql.append("SUM(CASE WHEN T.ANSWER LIKE '%,").append(index).append(",%' THEN 1 ELSE 0 END) AS '").append(index).append("'");
			if(i < questionEx.getAllIndexList().size() - 1){
				sql.append(",");
			}
			sql.append(" ");
		}
		
		sql.append("FROM SV_SURVEY_ANSWER_DETAIL T ");
		sql.append("WHERE T.SURVEY_ID = ? and T.QUESTION_ID = ?");
		return getUnique(sql.toString(), new Object[]{surveyId, questionId});
	}
}